//
//  BIDBillsLookoverManager.m
//  随手记01
//
//  Created by Yeming on 13-5-25.
//  Copyright (c) 2013年 邵瑞. All rights reserved.
//

#import "BIDBillsLookoverManager.h"
#import "BIDDatabaseManager.h"
#import "BIDABill.h"
#import "BIDATypes.h"
#import "FMDatabaseAdditions.h"
#import "BIDATypeSumAmount.h"
#define kTableName @"MessageInfoTable"//保存用户输入数据的表 331248897
#define kDatabaseHadYears @"hadYears"
#define kDatabaseHadMonths @"hadMonths"
#define kSpendMoney 1
#define kIncomeMoney 0
@interface BIDBillsLookoverManager ()
{
    FMDatabase *aDatabase;
}
@end
@implementation BIDBillsLookoverManager

+(BIDBillsLookoverManager *)shareBillsLookoverManager
{
    static BIDBillsLookoverManager *billsLookover=nil;
   
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        billsLookover=[[BIDBillsLookoverManager alloc] init];
    });
    return billsLookover;
}
-(id)init
{
    aDatabase=[BIDDatabaseManager SharedOperator].fmdbDataBase;
    return self;
}
#pragma mark --type of month
-(NSInteger)numberOfMonthInYear:(NSInteger)aYear
{
    NSInteger months=0;
    NSString *hadMonths=[NSString stringWithFormat:@"select count( distinct strftime('%%m',time)) from %@ where strftime('%%Y',time)=='%d'",kTableName,aYear];
    months=[aDatabase intForQuery:hadMonths];
    return months;
}
-(NSMutableArray *)arrayOfMonthsInYear:(NSInteger)aYear
{
    NSMutableArray *array=[[NSMutableArray alloc] init];
    NSString *hadMonths=[NSString stringWithFormat:@"select distinct strftime('%%m',time) %@ from %@ where strftime('%%Y',time)=='%d' order by time",kDatabaseHadMonths,kTableName,aYear];
    FMResultSet *months=[aDatabase executeQuery:hadMonths];
    while ([months next])
	 {
        NSInteger monthStr=[months intForColumn:kDatabaseHadMonths];
        [array addObject:[NSNumber numberWithInteger:monthStr]];
    }
    return array;
    [array release];
}
-(NSInteger)billsCountInMonth:(NSInteger)month andYear:(NSInteger)year
{
    NSInteger billCounts=0;
    NSString *lawfulMonth=[self monthBecomeLawful:month];
    NSString *strCount=[NSString stringWithFormat:@"select count(*) from %@ where strftime('%%Y',time)=='%d' and strftime('%%m',time)=='%@'",kTableName,year,lawfulMonth];
    billCounts=[aDatabase intForQuery:strCount];
    return billCounts;
}
-(NSInteger)totalAmountCountInMonth:(NSInteger)month andYear:(NSInteger)year
{
    NSInteger amount=0;
    NSString *lawfulMonth=[self monthBecomeLawful:month];

    NSString *strCount=[NSString stringWithFormat:@"select sum(amount) from %@ where strftime('%%Y',time)=='%d' and strftime('%%m',time)=='%@'",kTableName,year,lawfulMonth];
    amount=[aDatabase intForQuery:strCount];
    return amount;
}
-(NSInteger)totalAmountCountYearSpent:(NSInteger)years
{
             NSInteger yearAmount = 0;
           NSString *strYearCount=[NSString stringWithFormat:@"select sum(amount) from %@ where strftime('%%Y',time)=='%d'",kTableName,years];
              yearAmount = [aDatabase intForQuery:strYearCount];
              return yearAmount;
       
}
-(NSMutableArray *)billsArrayInMonth:(NSInteger)month andYear:(NSInteger)year
{
	   //amount,type_ID,subType_ID,time,other
    NSString *lawfulMonth=[self monthBecomeLawful:month];

    NSMutableArray *array=[[NSMutableArray alloc] init];
    NSString *strCount=[NSString stringWithFormat:@"select * from %@ where strftime('%%Y',time)=='%d' and strftime('%%m',time)=='%@' order by time",kTableName,year,lawfulMonth];
    FMResultSet *end=[aDatabase executeQuery:strCount];
    while ([end next])
    {
        BIDABill *bill=[[BIDABill alloc] init];
        float amount=[end doubleForColumn:@"amount"];
        NSInteger type_id=[end intForColumn:@"type_ID"];
        NSInteger subType_ID=[end intForColumn:@"subType_ID"];
        NSString *time=[end stringForColumn:@"time"];
        NSString *other=[end stringForColumn:@"other"];
        NSInteger payout_ID=[end intForColumn:@"messageInfo_ID"];
        NSInteger whichWay=[end intForColumn:@"whichWay"];
        bill.Camount=amount;
        bill.type_ID=type_id;
        bill.subType_ID=subType_ID;
        bill.Ctime=time;
        bill.Cdescription=other;
        bill.payoutId=payout_ID;
        bill.whichWay=whichWay;
        [array addObject:bill];
        [bill release];
    }
    return array;
    [array release];
}
-(float)spendAmountCountInMonth:(NSInteger)month andYear:(NSInteger)year
{
      float amount=0;
    NSString *lawfulMonth=[self monthBecomeLawful:month];

    NSString *strCount=[NSString stringWithFormat:@"select sum(amount) from %@ where strftime('%%Y',time)=='%d' and strftime('%%m',time)=='%@' and whichWay=%d",kTableName,year,lawfulMonth,kSpendMoney];
    amount=[aDatabase doubleForQuery:strCount];
    return amount;

}
-(float)incomeAmountCountInMonth:(NSInteger)month andYear:(NSInteger)year
{
     float amount=0;
    NSString *lawfulMonth=[self monthBecomeLawful:month];

    NSString *strCount=[NSString stringWithFormat:@"select sum(amount) from %@ where strftime('%%Y',time)=='%d' and strftime('%%m',time)=='%@' and whichWay=%d",kTableName,year,lawfulMonth,kIncomeMoney];
    amount=[aDatabase doubleForQuery:strCount];
    return amount;
}
#pragma mark --readBills key of type
-(NSInteger)numberOfMainTypesInYear:(NSInteger)aYear
{
    NSInteger types=0;
    NSString *hadTypes=[NSString stringWithFormat:@"select distinct type_ID from %@ where strftime('%%Y',time)=='%d'",kTableName,aYear];
    NSString *typeCount=[NSString stringWithFormat:@"select count(*) from %@",hadTypes];
    types=[aDatabase intForQuery:typeCount];
    return types;
    
}
-(NSMutableArray *)listOfMainTypesInYear:(NSInteger)aYear
{
    NSMutableArray *array=[[NSMutableArray alloc] init];
    NSString *strCount=[NSString stringWithFormat:@"select type_ID from %@ where strftime('%%Y',time)=='%d' ",kTableName,aYear];
    
    FMResultSet *end=[aDatabase executeQuery:strCount];
    while ([end next])
    {

        NSInteger type_id=[end intForColumn:@"type_ID"];
        
        [array addObject:[NSNumber numberWithInteger:type_id]];
    }
    return array;
    [array release];

}

-(NSInteger)numberOfBillsOfMainType:(NSInteger)aMainType_ID inYear:(NSInteger)aYear{
    NSInteger billCount=0;
     NSString *strCount=[NSString stringWithFormat:@"select count(*) from %@ where strftime('%%Y',time)=='%d' and  type_ID=%d",kTableName,aYear,aMainType_ID];
    billCount=[aDatabase intForQuery:strCount];
    return billCount;
}
-(NSMutableArray *)listOfBillsOfMainType:(NSInteger)aMainType_ID inYear:(NSInteger)aYear
{
    NSMutableArray *array=[[NSMutableArray alloc] init];
    NSString *strCount=[NSString stringWithFormat:@"select * from %@ where strftime('%%Y',time)=='%d' and type_ID=%d",kTableName,aYear,aMainType_ID];
    FMResultSet *end=[aDatabase executeQuery:strCount];
    while ([end next])
    {
        BIDABill *bill=[[BIDABill alloc] init];
        float amount=[end doubleForColumn:@"amount"];
        NSInteger type_id=[end intForColumn:@"type_ID"];
        NSInteger subType_ID=[end intForColumn:@"subType_ID"];
        NSString *time=[end stringForColumn:@"time"];
        NSString *other=[end stringForColumn:@"other"];
        NSInteger payout_ID=[end intForColumn:@"messageInfo_ID"];
        bill.Camount=amount;
        bill.type_ID=type_id;
        bill.subType_ID=subType_ID;
        bill.Ctime=time;
        bill.Cdescription=other;
        bill.payoutId=payout_ID;
        [array addObject:bill];
        [bill release];
    }
    return array;
    [array release];
}
#pragma mark--pie
-(NSInteger)numberOfMainTypesInMonth:(NSInteger)aMonth andYear:(NSInteger)aYear
{
    NSInteger typesCount=0;
    NSString *lawfulMonth=[self monthBecomeLawful:aMonth];

    NSString *strCount=[NSString stringWithFormat:@"select count(distinct type_ID) from %@ where strftime('%%Y',time)=='%d' and strftime('%%m',time)=='%@'",kTableName,aYear,lawfulMonth];
    typesCount=[aDatabase intForQuery:strCount];
    
    
    return typesCount;
}
-(NSMutableArray*)listOfMainTypesInMonth:(NSInteger)aMonth andYear:(NSInteger)aYear;
{
    NSMutableArray *array=[[NSMutableArray alloc] init];
    NSInteger type_id=0;
    NSString *lawfulMonth=[self monthBecomeLawful:aMonth];

     NSString *strCount=[NSString stringWithFormat:@"select distinct type_ID from %@ where strftime('%%Y',time)=='%d' and strftime('%%m',time)=='%@'",kTableName,aYear,lawfulMonth];
    FMResultSet  *end=[aDatabase executeQuery:strCount];
    while ([end next]) {
        type_id=[end intForColumn:@"type_ID"];
        [array addObject:[NSNumber numberWithInteger:type_id]];
    }
    return array;
    [array release];
}
-(NSMutableArray*)listOfAmountOfMainTypesInMonth:(NSInteger)aMonth andYear:(NSInteger)aYear andInType:(NSInteger)type_ID;
{
    NSMutableArray *array=[[NSMutableArray alloc] init];
        NSString *lawfulMonth=[self monthBecomeLawful:aMonth];
    NSMutableArray *types=[self listOfMainTypesInMonth:aMonth andYear:aYear];
                           


    for (int i=0; i<types.count; i++) {
        NSInteger type_id=[[types objectAtIndex:i] integerValue];
         NSString *strCount=[NSString stringWithFormat:@"select sum(amount) from %@ where strftime('%%Y',time)=='%d' and strftime('%%m',time)=='%@' and type_ID=%d",kTableName,aYear,lawfulMonth,type_id];
        NSInteger typeAmount=[aDatabase doubleForQuery:strCount];
        BIDATypeSumAmount *atype=[[BIDATypeSumAmount alloc] init];
        atype.type_id=type_id;
        atype.aTypeAmounts=typeAmount;
        atype.month=aMonth;
        atype.year=aYear;
        [array addObject:atype];
        [atype release];
    }
    return array;
    [array release];
   
}
-(NSMutableArray *)listOfBillsOfMainType:(NSInteger)type_id inMonth:(NSInteger)month andYear:(NSInteger)year;
{
    NSMutableArray *array=[[NSMutableArray alloc] init];
    NSString *lawfulMonth=[self monthBecomeLawful:month];

      NSString *strCount=[NSString stringWithFormat:@"select * from %@ where strftime('%%Y',time)=='%d' and strftime('%%m',time)=='%@' and type_ID=%d",kTableName,year,lawfulMonth,type_id];
    FMResultSet *end=[aDatabase executeQuery:strCount];
    while ([end next])
    {
        BIDABill *bill=[[BIDABill alloc] init];
        float amount=[end doubleForColumn:@"amount"];
        NSInteger type_id=[end intForColumn:@"type_ID"];
        NSInteger subType_ID=[end intForColumn:@"subType_ID"];
        NSString *time=[end stringForColumn:@"time"];
        NSString *other=[end stringForColumn:@"other"];
        NSInteger payout_ID=[end intForColumn:@"messageInfo_ID"];
        NSInteger whichWay=[end intForColumn:@"whichWay"];
        bill.Camount=amount;
        bill.type_ID=type_id;
        bill.subType_ID=subType_ID;
        bill.Ctime=time;
        bill.whichWay=whichWay;
        bill.Cdescription=other;
        bill.payoutId=payout_ID;
        [array addObject:bill];
        [bill release];
    }

    return array;
    [array release];
}
-(NSInteger)listOfBillsCountOfMainType:(NSInteger)type_id inMonth:(NSInteger)month andYear:(NSInteger)year
{
    NSString *lawfulMonth=[self monthBecomeLawful:month];
    
    NSString *strCount=[NSString stringWithFormat:@"select count(*) from %@ where strftime('%%Y',time)=='%d' and strftime('%%m',time)=='%@' and type_ID=%d",kTableName,year,lawfulMonth,type_id];
    NSInteger count=[aDatabase intForQuery:strCount];
    return count;
}
#pragma mark myself-deal month
-(NSString*)monthBecomeLawful:(NSInteger)month
{
    NSString *str;
    if (month<=9) {
        str = [NSString stringWithFormat:@"0%d",month];
        return str;
    }
    else
        str=[NSString stringWithFormat:@"%d",month];
        return str;
}
@end
